from pymongo import MongoClient
from tools_batch import *
from notneeded import *

import re
import sys
import xlrd
import pprint
import pymysql
import argparse
import datetime

parser = argparse.ArgumentParser()
parser.add_argument('-commit', help = 'if you need commit stock data to server please execute with -server', action = "store_true")
args = parser.parse_args()

# 如果需要上传预测结果到服务器
if args.commit:
    stock_db = pymysql.connect("rdsu70z23ct48w8t9584o.mysql.rds.aliyuncs.com", "jusrs9guttii", "qushu_2018", "boxandneedle", charset = "utf8")
    stock_cursor = stock_db.cursor()

client = MongoClient("localhost", 27017)
db = client.tb_scrapy
boxandneedle_coll = db.boxandneedle

today = datetime.datetime.now()

ExportOrderListPath = "data/ExportOrderList.xlsx"
ExportOrderDetailListPath = "data/ExportOrderDetailList.xlsx"

data = xlrd.open_workbook(ExportOrderDetailListPath)
table = data.sheet_by_index(0)

# 需要预测的宝贝
titles = []

for item in boxandneedle_coll.find({"time": {'$gt': datetime.datetime(today.year, today.month, today.day, 0, 0, 0)}}):
    continued = False

    for v in notneeded:
        if item['title'].find(v) != -1:
            continued = True

    if continued:
        continue

    titles.append((item['title'], getModelNums(item['title'])[-1]))

    if len(titles) == 24:
        break

pprint.pprint(titles)

print('文件读取完毕，准备开始清洗数据')

nrows = table.nrows
ncols = table.ncols

print('ExportOrderDetailList一共有{0}行'.format(nrows))

# 获取订单数据,key为订单id
orderList = getOrderList(ExportOrderListPath, titles)

datalist = getDataList(table, nrows, ncols, orderList, titles)

productsSummary = summaryProducts(datalist, titles)

print('请稍等,数据分析马上开始......')

accurateRates = []

for t in titles:
    title = t[0]

    print("#" * 30, title, "#" * 30)

    itemList = productsSummary[title]['data']

    # 某款宝贝的每个型号的库存、尺码的sku数据
    skuData = {}

    # 遍历每一天的库存数据，找到对应的销量数据
    for item in boxandneedle_coll.find({"title": re.compile(t[1])}):
        date, data, favcount = item["time"], item["data"], item["favcount"]

        for v in data:
            model, size, stock = v["model"], v["size"], v["stock"]

            if not model in skuData:
                skuData[model] = {}

            if not date in skuData[model]:
                skuData[model][date] = {
                    "stock": {},
                    "sale": {},
                    "price": {},
                    "favcount": favcount
                }

            if not size in skuData[model][date]["stock"]:
                skuData[model][date]["stock"][size] = 0

            skuData[model][date]["stock"][size] += int(stock)

    sizeSales = {}

    # 统计各个型号的尺码销量，辅助决策
    for item in itemList:
        # 没有颜色分类和尺码数据
        if len(item[5]) == 0:
            continue

        model, size = item[5].split(';')[0].split('：')[1], item[5].split(';')[1].split('：')[1]

        if not model in sizeSales:
            sizeSales[model] = {}

        if not size in sizeSales[model]:
            sizeSales[model][size] = 0

        sizeSales[model][size] += int(item[3])

    # 遍历该款式的宝贝的所有历史销售数据
    for item in itemList:
        date = item[-1]

        # 没有颜色分类和尺码数据
        if len(item[5]) == 0:
            continue

        model, size, price = item[5].split(';')[0].split('：')[1], item[5].split(';')[1].split('：')[1], float(item[2])

        if model in skuData:
            for key, value in skuData[model].items():
                if key.year == date.year and key.month == date.month and key.day == date.day:
                    if not size in value["sale"]:
                        value["sale"][size] = 0

                    value["sale"][size] += int(item[3])

                    if not size in value["price"]:
                        value["price"][size] = []

                    value['price'][size].append(price)
        else:
            pass
            # print(model, "can not match a stock data")

    actualSale = 0
    predictedSale = 0

    for key, _ in skuData.items():
        # if len(skuData[key]) <= 21:
        #     continue

        # 选取记录日期数超过14天(两周)的数据
        if len(skuData[key]) > 14:
            print("*" * 30, key, "*" * 30)
            skdata = []

            # 转化成数组方便后续的计算
            for k, v in skuData[key].items():
                v["time"] = k
                skdata.append(v)

            skuDataInWeeks = []

            if len(skdata) % 7 == 0:
                skdata = skdata[:-1]

            # 把数据按周分组
            for idx, val in enumerate(skdata):
                if idx % 7 == 0:
                    skuDataInWeeks.append([])

                skuDataInWeeks[idx // 7].append(val)

            predictedSkuStock = None
            actualSkuSale = None
            endDate = None

            historySkuDataInWeeks = []

            for idx, weekData in enumerate(skuDataInWeeks):
                if len(weekData) == 7:
                    if len(sys.argv) == 2 and sys.argv[1] == 'detail':
                        for k, v in enumerate(weekData):
                            pprint.pprint(v)
                            print()

                    weekStock = countTotalStockByWeek(countTotalSaleByWeek(weekData), skuDataInWeeks[idx + 1][0])
                    weekSale = countTotalSaleByWeek(weekData)

                    print("库存", weekStock)
                    print("销售", weekSale)

                    if len(skuDataInWeeks[idx + 1]) == 7:
                        if len(sys.argv) == 2 and sys.argv[1] == 'detail':
                            print('-' * 100)

                        historySkuDataInWeeks.append({
                            "stock": weekStock,
                            "sale": weekSale
                        })
                        endDate = weekData[-1]['time']
                        print()
                    else:
                        predictedSkuStock = weekStock
                        actualSkuSale = weekSale

            print()
            sizeSale = countSizeSaleEndByDate(itemList, key, endDate)
            predictedSkuSaleCount = predictSkuSaleCount(historySkuDataInWeeks, predictedSkuStock, actualSkuSale, key, sizeSale)

            if countToalSale(predictedSkuSaleCount):
                accurateRate = round((1 - abs(countToalSale(predictedSkuSaleCount) - countToalSale(actualSkuSale)) / countToalSale(predictedSkuSaleCount)) * 100, 2)
            elif countToalSale(predictedSkuSaleCount) == 0 and countToalSale(actualSkuSale) == 0:
                accurateRate = 100
            else:
                accurateRate = 0

            if accurateRate > 0:
                accurateRates.append(accurateRate)

            print('预测', predictedSkuSaleCount, '预测:', countToalSale(predictedSkuSaleCount), '件', ',', '实际', countToalSale(actualSkuSale), '件', '准确率', str(accurateRate) + '%')
            print()
            print('尺码', sorted(sizeSale.items(), key = lambda d: d[1], reverse = True))

            if args.commit:
                for size, count in predictedSkuSaleCount.items():
                    sql = "INSERT INTO predictSale(TITLE,MODEL, SHOWSIZE,COUNT) VALUES " + str((title, key, size, count))
                    stock_cursor.execute(sql)
                    stock_db.commit()
                    print("-sending-", title, key, size, count)

print()
print('准确率数组:', accurateRates)
print()
print('平均准确率:', str(round(avg(accurateRates), 2)) + '%')

report()

if args.commit:
    stock_db.close()
    print('数据上传结束')
